USE [master]

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'PetShop')
BEGIN
DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
	SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'PetShop' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
	SET @sql = 'KILL ' + CAST(@spid AS varchar)
	EXEC(@sql)
	FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [PetShop]
END
GO

CREATE DATABASE [PetShop]
GO


USE [PetShop]



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ITSEQ_NEXT_VAL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ITSEQ_NEXT_VAL]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Account]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreditCard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CreditCard]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Category]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Product]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Item]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Category_Products]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product_Items]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Product_Items]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ITCLASSES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ITCLASSES]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ITSEQ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ITSEQ]
GO


CREATE TABLE [dbo].[Account] (
	[OID] [int] NOT NULL,
	[FirstName] [varchar] (30) NOT NULL,
	[LastName] [varchar] (30) NOT NULL,
	[StreetAddress] [varchar] (30) NOT NULL,
	[PostalCode] [varchar] (30) NOT NULL,
	[City] [varchar] (30) NOT NULL,
	[StateOrProvince] [varchar] (30) NOT NULL,
	[Country] [varchar] (30) NOT NULL,
	[TelephoneNumber] [varchar] (30) NOT NULL,
	[EMail] [varchar] (30) NOT NULL,
	[Login] [varchar] (30) NOT NULL,
	[Password] [varchar] (30) NOT NULL,
	[IWantMyList] [smallint] NOT NULL,
	[IWantPetTips] [smallint] NOT NULL,
	[FavoriteLanguage] [varchar] (30) NOT NULL
	PRIMARY KEY ([OID])
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CreditCard] (
	[OID] [int] NOT NULL,
	[Number] [varchar] (30) NOT NULL,
	[CardType] [varchar] (30) NOT NULL,
	[ExpiryMonth] [varchar] (30) NOT NULL,
	[ExpiryYear] [varchar] (30) NOT NULL
	PRIMARY KEY ([OID])
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Category] (
	[OID] [int] NOT NULL,
	[Name] [varchar] (30) NOT NULL,
	[AdvicePhoto] [varchar] (30) NOT NULL
	PRIMARY KEY ([OID])
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Product] (
	[OID] [int] NOT NULL,
	[Name] [varchar] (30) NOT NULL,
	[Description] [varchar] (30) NOT NULL
	PRIMARY KEY ([OID])
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Item] (
	[OID] [int] NOT NULL,
	[Name] [varchar] (30) NOT NULL,
	[Description] [varchar] (30) NOT NULL,
	[Price] [int] NOT NULL,
	[Currency] [varchar] (30) NOT NULL,
	[Photo] [varchar] (30) NOT NULL
	PRIMARY KEY ([OID])
) ON [PRIMARY]
GO

CREATE TABLE Category_Products (
	[Category] [int] NOT NULL,
	[Products] [int] NOT NULL
	PRIMARY KEY ([Category], [Products])
)
GO

CREATE TABLE Product_Items (
	[Product] [int] NOT NULL,
	[Items] [int] NOT NULL
	PRIMARY KEY ([Product], [Items])
)
GO

CREATE TABLE [dbo].[ITCLASSES] (
	[CLASSID] [int] NOT NULL ,
	[CLASSNAME] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ITSEQ] (
	[SEQID] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CURVAL] [int] NOT NULL 
) ON [PRIMARY]
GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE ITSEQ_NEXT_VAL (@seqid char(32), @ident_incr int, @result int output) as
begin
  declare @curval int 
  begin tran
    
    select @curval = CURVAL
    from ITSEQ
    where SEQID = 'ITOBJECTSID'
    
    update ITSEQ
    set CURVAL = @curval + @ident_incr
    where SEQID = 'ITOBJECTSID'
    
    select @result = @curval + 1
  commit tran
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
	
INSERT INTO ITCLASSES (CLASSID, CLASSNAME) VALUES (1, 'Account')

INSERT INTO ITCLASSES (CLASSID, CLASSNAME) VALUES (2, 'CreditCard')

INSERT INTO ITCLASSES (CLASSID, CLASSNAME) VALUES (3, 'Category')

INSERT INTO ITCLASSES (CLASSID, CLASSNAME) VALUES (4, 'Product')

INSERT INTO ITCLASSES (CLASSID, CLASSNAME) VALUES (5, 'Item')

GO
INSERT INTO ITSEQ (SEQID, CURVAL) VALUES ('ITOBJECTSID', 1)
GO

